var express = require('express') //引入express框架
var mysql = require('mysql') //引入mysql
var bodyParser = require('body-parser'); //引入处理数据
const { json } = require('body-parser');
var app = express(); //实例化
app.use(bodyParser.json()); //实例化接收参数json
app.use(bodyParser.urlencoded({extended:false})); //实例化接收formdata
var dbConfig = {
    host:'127.0.0.1',
    user:'root',
    password:'root',
    database:'nodebase'
}
//连接数据库配置

function handleError(err) {
    if (err) {
        // 如果是连接异常，自动重新连接
        console.log('err code:' + err.code);
        if (err.code === 'PROTOCOL_CONNECTION_LOST' || err.code === 'PROTOCOL_ENQUEUE_AFTER_FATAL_ERROR' || err.code === 'ETIMEDOUT') {
            connectFunc();
        } else {
            console.error(err.stack || err);
        }
    }
}

function connectFunc() {
    connect = mysql.createConnection(dbConfig);
    connect.connect(handleError);
    connect.on('error', handleError);
}
var connect ;

connectFunc();



app.listen('8889',()=>{ //设置node服务端口 启动服务
    console.log('服务启动成功');
})
app.all('*', function(req, res, next) {
    // res.header("Access-Control-Allow-Origin", "http://localhost:8889");
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "X-Requested-With,Content-Type");
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    next();
  });

app.get('/getData',(req,res)=>{ //登录方法 GET
    var sql = 'SELECT * FROM websites' //查询用
    connect.query(sql,(err,result)=>{
        res.json({code:200,body:result,message:'请求成功'})
    })
    
})

// 登录
app.get('/login',(req,res)=>{ //登录方法
    var sql = 'SELECT * FROM user WHERE username = ?' //查询账号是否存在user表中
    console.log(req.query.username);
    connect.query(sql,req.query.username,(err,result)=>{ //执行sql
        let username = req.query.username; //接收请求参数
        let password = req.query.password
        if(!username){
            return res.json({code:202,message:'账号不得为空'})
        }
        if(!password){
            return res.json({code:202,message:'密码不得为空'})
        }
        if(result.length>0){ //查询数据结果
            if(result[0].username == username && result[0].password == password){ //匹配结果
                return res.json({code:200,message:'登陆成功',body:result[0]})
            }else{
                return res.json({code:201,message:'账号或密码错误'})
            }
            
        }else{
            return res.json({code:203,message:'账号不存在'}) //没查到数据
        }
    })
})

//注册
app.post('/reg',(req,res)=>{
    let sql = 'INSERT INTO user(username,password,name) VALUES(?,?,?)'
    let username = req.body.username;
    let password = req.body.password;
    let name = req.body.name;
    if(!username){
        return res.json({code:402,message:'账号不得为空'})
    }
    if(!password){
        return res.json({code:402,message:'密码不得为空'})
    }
    if(!name){
        return res.json({code:402,message:'姓名不得为空'})
    }
    let c = 'SELECT * FROM user WHERE username=?'
    connect.query(c,username,(err,result)=>{
        if(result.length>0){
            return res.json({code:401,message:'账号已存在'})
        }else{
            let data = [username,password,name]
            connect.query(sql,data,(errs,results)=>{
                if(results.insertId>0){
                    return res.json({code:200,message:'注册成功'})
                }else{
                    return res.json({code:500,message:'系统错误'})
                }
                if(errs){
                    return res.json({code:500,message:'系统错误'})
                }
            })
        }
    })
    
})

//评论
app.post('/addIssue',(req,res)=>{
    let sql = 'INSERT INTO dissue(title,sorttitle,userid,content,time,utime) VALUES(?,?,?,?,?,?)'
    let time = new Date().getTime();
    let utime = '';
    let title = req.body.title;
    let sorttitle = req.body.sorttitle;
    let userid = req.body.userid;
    let content = req.body.content;
    let data = [title,sorttitle,userid,content,time,utime]
    connect.query(sql,data,(err,result)=>{
        if(err){
            return res.json({code:202,message:'系统错误',body:err})
        }
        return res.json({code:200,message:'添加成功',body:''})
    })
})
//查询当前用户发布
app.get('/getIssue',(req,res)=>{
    let sql = 'SELECT * FROM dissue WHERE userid = ?'
    let userid = req.query.userid
    // console.log(req);
    connect.query(sql,userid,(err,result)=>{
        if(err){
            return res.json({code:202,message:'系统错误',body:err})
        }
        return res.json({code:200,body:result,message:'success'})
    })
})
//添加栏目
app.post('/addClunm',(req,res)=>{
    let sql = 'INSERT INTO clunm(name,bz,time,user) VALUES(?,?,?,?)'
    let name = req.body.name;
    let bz = req.body.bz;
    let time = req.body.time;
    let user = req.body.user;
    if(!name){
        return res.json({code:201,message:'栏目名称不得为空'})
    }
    let data = [name,bz,time,user]
    connect.query(sql,data,(err,result)=>{
        if(result){
            return res.json({code:200,message:'添加成功',body:result[0]})
        }
        if(err){
            return res.json({code:2001,message:'系统错误',body:err})
        }
    })
})
//获取栏目
app.get('/getClunm',(req,res)=>{
    let sql = 'SELECT * FROM clunm'
    connect.query(sql,(err,result)=>{
        return res.json({code:200,body:result,message:'查询成功'})
    })
})
//编辑栏目
app.post('/updateClunm',(req,res)=>{
    let sql = 'UPDATE clunm SET name=?, bz=? ,time=?,user=? WHERE id=?'
    let {name,bz,time,user,id} = req.body;
    let data = [name,bz,time,user,id]
    console.log(req);
    connect.query(sql,data,(err,result)=>{
        if(result){
            return res.json({code:200,message:'修改成功',body:result[0]})
        }else{
            return res.json({code:201,message:'修改失败',body:err})
        }
    })
    
})

//删除方法
app.get('/delClunm',(req,res)=>{
    let sql = 'DELETE FROM clunm WHERE id=?'
    let id = req.query.id
    if(!id){
        return res.json({code:201,message:'参数错误'})
    }
    connect.query(sql,id,(err,result)=>{
        if(result){
            return res.json({code:200,message:'删除成功',body:result[0]})
        }else{
            return res.json({code:201,message:'删除失败',body:err})
        }
    })
})
app.post('')
//导航添加
app.post('/addNavigator',(req,res)=>{
    let sql = 'INSERT INTO navigator(title,description,pic,url,userid,time,type) VALUES(?,?,?,?,?,?,?)'
    let {title,description,pic,url,userid,type} = req.body;
    if(!title){
        return res.json({code:201,message:'标题不得为空'})
    }
    if(!url){
        return res.json({code:201,message:'标题不得为空'})
    }
    let time = new Date().getTime();
    let data = [title,description,pic,url,userid,time,type]
    connect.query(sql,data,(err,result)=>{
        console.log(err);
        if(err){
            return res.json({code:203,message:'参数错误',body:err})
        }
        return res.json({code:200,message:'添加成功',body:result})
    })
})
//导航修改
app.post('/updateNavigator',(req,res)=>{
    let sql = 'UPDATE navigator SET title=? , description=? , pic=? , url=? , userid=? , time=? ,type=? WHERE id=?'
    let  {title,description,pic,url,userid,type,id} = req.body;
    let time = new Date().getTime();
    let data = [title,description,pic,url,userid,time,type,id]
    connect.query(sql,data,(err,result)=>{
        console.log(err);
        if(err){
            return err
        }
        return res.json({code:200,message:'修改成功',body:result})
    })

})
//导航删除
app.get('/delNavigator',(req,res)=>{
    let id = req.query.id;
    let sql = 'DELETE FROM navigator WHERE id=?'
    if(!id){
        return res.json({code:201,message:'参数错误'})
    }
    connect.query(sql,id,(err,result)=>{
        if(err){
            return res.json({code:202,message:'系统错误',body:err})
        }
        return res.json({code:200,message:'删除成功'})
    })
})
//查询导航
app.get('/getNavigator',(req,res)=>{
    let sql = 'select * from navigator limit ?,?';
    let {current,size} = req.query;
    current = (current-1)*size
    size = Number(size)
    let data = [current,size]
    console.log(data);
    connect.query(sql,data,(err,result)=>{
        if(err){
            return res.json(err)
        }
        return res.json({code:200,message:'查询成功',body:result})
    })
})
//测试500 
app.get('/test',(req,res)=>{
    let sql = 'SELECT * FROM clunm WHERE id=?'
    connect.query(sql,'1',(err,result)=>{
        if(err){
            return res.send(err)
        }
        return res.json(result)
    })
})
//get请求中获取req参数可以使用req.query query是nodejs自带的获取手段

//post请求可以使用req.body这个方法是由body-parserr提供
